package birs.controller.staff.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.staff.ChartModel;
import birs.model.staff.HeaderDialog;
import birs.model.staff.StaffAcad;
import birs.model.staff.StaffAcadByFac;
import birs.model.staff.VHrmStaffEduHighest;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STF002Task {
	private List<VHrmStaffEduHighest> vHrmStaffEduHighestList = null;
	
	Connection con = null;
	
	public STF002Task(Connection con){
		this.con = con;
	}
	
	public void getStaffAcad(HttpServletRequest request, HttpServletResponse response) throws IOException{
		ResultSetMapper<VHrmStaffEduHighest> vHrmStaffEduH = new ResultSetMapper<VHrmStaffEduHighest>();
		if(vHrmStaffEduHighestList == null){
			vHrmStaffEduHighestList = vHrmStaffEduH.mapRersultSetToObject(getVHrmStaffEduHighestData(), VHrmStaffEduHighest.class);
		}
		
		StaffAcad educationFac = new StaffAcad();
		StaffAcad demonstationSchool = new StaffAcad();
		StaffAcad agriculualFac = new StaffAcad();
		StaffAcad techGemSciFac = new StaffAcad();
		StaffAcad humanSocSciFac = new StaffAcad();
		StaffAcad manageSciFac = new StaffAcad();
		StaffAcad sciTechFac = new StaffAcad();
		StaffAcad lawFac = new StaffAcad();
		StaffAcad comSciInfoTechFac = new StaffAcad();
		StaffAcad communicateFac = new StaffAcad();
		StaffAcad gemSciArtFac = new StaffAcad();
		for (VHrmStaffEduHighest vHrmStaffEduHighest : vHrmStaffEduHighestList) {
			if(vHrmStaffEduHighest.getTeacherMale()==1 || vHrmStaffEduHighest.getApmale()==1 
					|| vHrmStaffEduHighest.getSpMale()==1 || vHrmStaffEduHighest.getpMale()==1 || vHrmStaffEduHighest.getUndefineMale()==1){
				if(vHrmStaffEduHighest.getDepartmentId()==62){
					educationFac.setMaleStaffAmount(educationFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==68){
					demonstationSchool.setMaleStaffAmount(demonstationSchool.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==70){
					agriculualFac.setMaleStaffAmount(agriculualFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==77){
					techGemSciFac.setMaleStaffAmount(techGemSciFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==84){
					humanSocSciFac.setMaleStaffAmount(humanSocSciFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==91){
					manageSciFac.setMaleStaffAmount(manageSciFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==97){
					sciTechFac.setMaleStaffAmount(sciTechFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==124){
					lawFac.setMaleStaffAmount(lawFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==335){
					comSciInfoTechFac.setMaleStaffAmount(comSciInfoTechFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==354){
					communicateFac.setMaleStaffAmount(communicateFac.getMaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==393){
					gemSciArtFac.setMaleStaffAmount(gemSciArtFac.getMaleStaffAmount()+1);
				}
			}else {
				if(vHrmStaffEduHighest.getDepartmentId()==62){
					educationFac.setFemaleStaffAmount(educationFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==68){
					demonstationSchool.setFemaleStaffAmount(demonstationSchool.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==70){
					agriculualFac.setFemaleStaffAmount(agriculualFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==77){
					techGemSciFac.setFemaleStaffAmount(techGemSciFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==84){
					humanSocSciFac.setFemaleStaffAmount(humanSocSciFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==91){
					manageSciFac.setFemaleStaffAmount(manageSciFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==97){
					sciTechFac.setFemaleStaffAmount(sciTechFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==124){
					lawFac.setFemaleStaffAmount(lawFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==335){
					comSciInfoTechFac.setFemaleStaffAmount(comSciInfoTechFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==354){
					communicateFac.setFemaleStaffAmount(communicateFac.getFemaleStaffAmount()+1);
				}else if(vHrmStaffEduHighest.getDepartmentId()==393){
					gemSciArtFac.setFemaleStaffAmount(gemSciArtFac.getFemaleStaffAmount()+1);
				}
			}
		}
		educationFac.setSequence(1);
		educationFac.setFacultyId(62);
		educationFac.setFacultyName("คณะครุศาสตร์");
		educationFac.setTotalStaff(educationFac.getMaleStaffAmount()+educationFac.getFemaleStaffAmount());
		
		demonstationSchool.setSequence(2);
		demonstationSchool.setFacultyId(68);
		demonstationSchool.setFacultyName("โรงเรียนสาธิต");
		demonstationSchool.setTotalStaff(demonstationSchool.getMaleStaffAmount()+demonstationSchool.getFemaleStaffAmount());
		
		agriculualFac.setSequence(3);
		agriculualFac.setFacultyId(70);
		agriculualFac.setFacultyName("คณะเทคโนโลยีการเกษตร");
		agriculualFac.setTotalStaff(agriculualFac.getMaleStaffAmount()+agriculualFac.getFemaleStaffAmount());
		
		techGemSciFac.setSequence(4);
		techGemSciFac.setFacultyId(77);
		techGemSciFac.setFacultyName("คณะเทคโนโลยีอุตสาหกรรมและอัญมณีศาสตร์");
		techGemSciFac.setTotalStaff(techGemSciFac.getMaleStaffAmount()+techGemSciFac.getFemaleStaffAmount());
		
		humanSocSciFac.setSequence(5);
		humanSocSciFac.setFacultyId(84);
		humanSocSciFac.setFacultyName("คณะมนุษยศาสตร์และสังคมศาสตร์");
		humanSocSciFac.setTotalStaff(humanSocSciFac.getMaleStaffAmount()+humanSocSciFac.getFemaleStaffAmount());
		
		manageSciFac.setSequence(6);
		manageSciFac.setFacultyId(91);
		manageSciFac.setFacultyName("คณะวิทยาการจัดการ");
		manageSciFac.setTotalStaff(manageSciFac.getMaleStaffAmount()+manageSciFac.getFemaleStaffAmount());
		
		sciTechFac.setSequence(7);
		sciTechFac.setFacultyId(97);
		sciTechFac.setFacultyName("คณะวิทยาศาสตร์และเทคโนโลยี");
		sciTechFac.setTotalStaff(sciTechFac.getMaleStaffAmount()+sciTechFac.getFemaleStaffAmount());
		
		lawFac.setSequence(8);
		lawFac.setFacultyId(124);
		lawFac.setFacultyName("คณะนิติศาสตร์");
		lawFac.setTotalStaff(lawFac.getMaleStaffAmount()+lawFac.getFemaleStaffAmount());
		
		comSciInfoTechFac.setSequence(9);
		comSciInfoTechFac.setFacultyId(335);
		comSciInfoTechFac.setFacultyName("คณะวิทยาการคอมพิวเตอร์และเทคโนโลยีสารสนเทศ");
		comSciInfoTechFac.setTotalStaff(comSciInfoTechFac.getMaleStaffAmount()+comSciInfoTechFac.getFemaleStaffAmount());
		
		communicateFac.setSequence(10);
		communicateFac.setFacultyId(354);
		communicateFac.setFacultyName("คณะนิเทศศาสตร์");
		communicateFac.setTotalStaff(communicateFac.getMaleStaffAmount()+communicateFac.getFemaleStaffAmount());
		
		gemSciArtFac.setSequence(11);
		gemSciArtFac.setFacultyId(393);
		gemSciArtFac.setFacultyName("คณะอัญมณีศาสตร์และประยุกต์ศิลป์");
		gemSciArtFac.setTotalStaff(gemSciArtFac.getMaleStaffAmount()+gemSciArtFac.getFemaleStaffAmount());
		
		int totalStaff =(educationFac.getTotalStaff() + demonstationSchool.getTotalStaff()+agriculualFac.getTotalStaff() + techGemSciFac.getTotalStaff() + humanSocSciFac.getTotalStaff() + manageSciFac.getTotalStaff() 
				+ sciTechFac.getTotalStaff() + lawFac.getTotalStaff() + comSciInfoTechFac.getTotalStaff() + communicateFac.getTotalStaff() + gemSciArtFac.getTotalStaff());
		
		educationFac.setPercentage(getPercenttage(educationFac.getTotalStaff(), totalStaff));
		demonstationSchool.setPercentage(getPercenttage(demonstationSchool.getTotalStaff(), totalStaff));
		agriculualFac.setPercentage(getPercenttage(agriculualFac.getTotalStaff(), totalStaff));
		techGemSciFac.setPercentage(getPercenttage(techGemSciFac.getTotalStaff(), totalStaff));
		humanSocSciFac.setPercentage(getPercenttage(humanSocSciFac.getTotalStaff(), totalStaff));
		manageSciFac.setPercentage(getPercenttage(manageSciFac.getTotalStaff(), totalStaff));
		sciTechFac.setPercentage(getPercenttage(sciTechFac.getTotalStaff(), totalStaff));
		lawFac.setPercentage(getPercenttage(lawFac.getTotalStaff(), totalStaff));
		comSciInfoTechFac.setPercentage(getPercenttage(comSciInfoTechFac.getTotalStaff(), totalStaff));
		communicateFac.setPercentage(getPercenttage(communicateFac.getTotalStaff(), totalStaff));
		gemSciArtFac.setPercentage(getPercenttage(gemSciArtFac.getTotalStaff(), totalStaff));
		
		List<StaffAcad> tBody = new ArrayList<StaffAcad>();
		tBody.add(educationFac);
		tBody.add(demonstationSchool);
		tBody.add(agriculualFac);
		tBody.add(techGemSciFac);
		tBody.add(humanSocSciFac);
		tBody.add(manageSciFac);
		tBody.add(sciTechFac);
		tBody.add(lawFac);
		tBody.add(comSciInfoTechFac);
		tBody.add(communicateFac);
		tBody.add(gemSciArtFac);
		
		List<Object> summaryStaffList = new ArrayList<Object>();
		summaryStaffList.add(tBody);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(summaryStaffList);
		System.out.println(resultJson);
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	
	}
	
	public void getDrilldown(HttpServletRequest request, HttpServletResponse response) throws IOException{
		ResultSetMapper<VHrmStaffEduHighest> vHrmStaffEduH = new ResultSetMapper<VHrmStaffEduHighest>();
		if(vHrmStaffEduHighestList == null){
			vHrmStaffEduHighestList = vHrmStaffEduH.mapRersultSetToObject(getVHrmStaffEduHighestData(), VHrmStaffEduHighest.class);
		}
		ResultSetMapper<VHrmStaffEduHighest> vHrmStaffEduHDetail = new ResultSetMapper<VHrmStaffEduHighest>();
		List<VHrmStaffEduHighest> vHrmStaffEduHighestDetailList = vHrmStaffEduHDetail.mapRersultSetToObject(getVHrmStaffEduHighestDataDetail(request.getParameter("p1")), VHrmStaffEduHighest.class);
		List<StaffAcadByFac> tBodyDetail = new ArrayList<StaffAcadByFac>();
		List<Object> detailList = new ArrayList<Object>();
		List<ChartModel> donutChartDetail = new ArrayList<ChartModel>();
		HeaderDialog headerDialog = new HeaderDialog();
		int seq =0;
		if("62".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะครุศาสตร์");
		}else if("68".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร โรงเรียนสาธิต");
		}else if("70".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะเทคโนโลยีการเกษตร");
		}else if("77".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะเทคโนโลยีอุตสาหกรรมและอัญมณีศาสตร์");
		}else if("84".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะมนุษยศาสตร์และสังคมศาสตร์");
		}else if("91".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะวิทยาการจัดการ");
		}else if("97".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะวิทยาศาสตร์และเทคโนโลยี");
		}else if("124".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะนิติศาสตร์");
		}else if("335".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะวิทยาการคอมพิวเตอร์และเทคโนโลยีสารสนเทศ");
		}else if("354".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะนิเทศศาสตร์");
		}else if("393".equals(request.getParameter("p1"))){
			headerDialog.setName("บุคลากร คณะอัญมณีศาสตร์และประยุกต์ศิลป์");
		}
		
		if(request.getParameter("p2").equals("facDetail")){
			
			ChartModel teacherChart = new ChartModel();
			teacherChart.setLabel("อาจารย์");
			ChartModel asstProfChart = new ChartModel();
			asstProfChart.setLabel("ผู้ช่วยศาสตราจารย์");
			ChartModel assocProfChart = new ChartModel();
			assocProfChart.setLabel("รองศาสตราจารย์");
			ChartModel profChart = new ChartModel();
			profChart.setLabel("ศาสตราจารย์");
			ChartModel unifChart = new ChartModel();
			unifChart.setLabel("ไม่ระบุ");
			
			for (VHrmStaffEduHighest vHrmStaffEduHighest : vHrmStaffEduHighestDetailList) {
				StaffAcadByFac detail = new StaffAcadByFac();
				detail.setSequence(++seq);
				detail = getDetailStaff(vHrmStaffEduHighest, detail);
				
				tBodyDetail.add(detail);
				
				if(vHrmStaffEduHighest.getTeacherMale()==1||vHrmStaffEduHighest.getTeacherFemale()==1){
					teacherChart.setValue(teacherChart.getValue()+1);
				}else if(vHrmStaffEduHighest.getApmale()==1||vHrmStaffEduHighest.getApFemale()==1){
					asstProfChart.setValue(asstProfChart.getValue()+1);
				}else if(vHrmStaffEduHighest.getSpMale()==1||vHrmStaffEduHighest.getSpFemale()==1){
					assocProfChart.setValue(assocProfChart.getValue()+1);
				}else if(vHrmStaffEduHighest.getpMale()==1||vHrmStaffEduHighest.getpFemale()==1){
					profChart.setValue(profChart.getValue()+1);
				}else if(vHrmStaffEduHighest.getUndefineMale()==1||vHrmStaffEduHighest.getUndefineFemale()==1){
					unifChart.setValue(unifChart.getValue()+1);
				}
			}
			
			donutChartDetail.add(teacherChart);
			donutChartDetail.add(asstProfChart);
			donutChartDetail.add(assocProfChart);
			donutChartDetail.add(profChart);
			donutChartDetail.add(unifChart);
			
		}else if(request.getParameter("p2").equals("male")){
			headerDialog.setName(headerDialog.getName()+" เพศชาย");
			for (VHrmStaffEduHighest vHrmStaffEduHighest : vHrmStaffEduHighestDetailList) {
				if(vHrmStaffEduHighest.getTeacherMale()==1 || vHrmStaffEduHighest.getApmale()==1 
						|| vHrmStaffEduHighest.getSpMale()==1 || vHrmStaffEduHighest.getpMale()==1 || vHrmStaffEduHighest.getUndefineMale()==1){
					StaffAcadByFac maleDetail = new StaffAcadByFac();
					maleDetail.setSequence(++seq);
					maleDetail = getDetailStaff(vHrmStaffEduHighest, maleDetail);
					tBodyDetail.add(maleDetail);
				}
			}
		}else if(request.getParameter("p2").equals("female")){
			headerDialog.setName(headerDialog.getName()+" เพศหญิง");
			for (VHrmStaffEduHighest vHrmStaffEduHighest : vHrmStaffEduHighestDetailList) {
				if(vHrmStaffEduHighest.getTeacherFemale()==1 || vHrmStaffEduHighest.getApFemale()==1 
						|| vHrmStaffEduHighest.getSpFemale()==1 || vHrmStaffEduHighest.getpFemale()==1 || vHrmStaffEduHighest.getUndefineFemale()==1){
					StaffAcadByFac femaleDetail = new StaffAcadByFac();
					femaleDetail.setSequence(++seq);
					femaleDetail = getDetailStaff(vHrmStaffEduHighest, femaleDetail);
					tBodyDetail.add(femaleDetail);
				}
			}
		}
//		Map<String,String> tHeaderDetail = new TreeMap<String,String>();
//		tHeaderDetail.put("header1", "�ӴѺ");
//		tHeaderDetail.put("header2", "����-ʡ��");
//		tHeaderDetail.put("header3", "���˹觷ҧ�Ԫҡ��");
//		tHeaderDetail.put("header4", "�дѺ����֡��/�زԡ���֡��");
//		tHeaderDetail.put("header5", "�Ԫ��͡");
//		tHeaderDetail.put("header6", "ʶҹ�֡��");
//		
//		detailList.add(tHeaderDetail);
		detailList.add(headerDialog);
		detailList.add(tBodyDetail);
		detailList.add(donutChartDetail);
		
		String resultDetail = ConvertDataType.getInstance().objectToJasonArray(detailList);
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultDetail);
		out.close();
	
	}
	
private StaffAcadByFac getDetailStaff(VHrmStaffEduHighest vHrmTemp,StaffAcadByFac detailTemp){
		
		detailTemp.setName(vHrmTemp.getPrefixName()+vHrmTemp.getStaffName()+" "+vHrmTemp.getStaffSurname());
		if(vHrmTemp.getAcadPosName() != null){
			detailTemp.setAcadPosType(vHrmTemp.getAcadPosName());
		}else{
			detailTemp.setAcadPosType("N/A");
		}
		
		if(vHrmTemp.getDegreeLevelName() !=null && vHrmTemp.getDegreeName()!=null ){
			detailTemp.setDegree(vHrmTemp.getDegreeLevelName()+"/"+vHrmTemp.getDegreeName());
		}else if(vHrmTemp.getDegreeLevelName() != null){
			detailTemp.setDegree(vHrmTemp.getDegreeLevelName()+"/ N/A");
		}else if(vHrmTemp.getDegreeName()!=null){
			detailTemp.setDegree("N/A /"+vHrmTemp.getDegreeName());
		}else{
			detailTemp.setDegree("N/A");
		}
		
		if(vHrmTemp.getMajorName() != null){
			detailTemp.setMajor(vHrmTemp.getMajorName());
		}else{
			detailTemp.setMajor("N/A");
		}
		
		if(vHrmTemp.getUniversityName()!=null && vHrmTemp.getCountryName() != null){
			detailTemp.setUniversityAndCountry(vHrmTemp.getUniversityName()+"/"+vHrmTemp.getCountryName());
		}else if(vHrmTemp.getUniversityName() != null){
			detailTemp.setUniversityAndCountry(vHrmTemp.getUniversityName()+"/ N/A");
		}else if(vHrmTemp.getCountryName() != null){
			detailTemp.setUniversityAndCountry("N/A /"+vHrmTemp.getCountryName());
		}else {
			detailTemp.setUniversityAndCountry("N/A");
		}
		return detailTemp;
	}
	
	private ResultSet getVHrmStaffEduHighestData(){
		String sql =
				"SELECT V.TMALE, V.TFEMALE, V.APMALE,V.APFEMALE,V.SPMALE,V.SPFEMALE,V.PMALE,V.PFEMALE,V.NULLMALE,V.NULLFEMALE, V.DEPARTMENTID , "
				+" V.PREFIXNAME,V.STAFFNAME,V.STAFFSURNAME,V.ACADPOSITIONID,A.ACADPOSITIONNAME,V.DEGREELEVEL,SYS.BYTEDES,V.DEGREEID,D.DEGREENAME, "
				+" V.MAJORID,M.MAJORNAME,V.UNIVERSITYID,U.UNIVERSITYNAME,V.COUNTRYID,C.COUNTRYNAME "
				+" FROM V_HRM_STAFFEDU_HIGHEST V LEFT JOIN ACADPOSITION A ON V.ACADPOSITIONID = A.ACADPOSITIONID  "
				+" LEFT JOIN  MAJOR M ON V.MAJORID = M.MAJORID "
				+" LEFT JOIN STAFF S ON V.STAFFID = S.STAFFID "
				+" LEFT JOIN DEGREE D ON V.DEGREEID = D.DEGREEID "
				+" LEFT JOIN UNIVERSITY U ON V.UNIVERSITYID = U.UNIVERSITYID "
				+" LEFT JOIN SYSBYTEDES SYS ON V.DEGREELEVEL = SYS.BYTECODE AND SYS.TABLENAME = \'DEGREE\' AND SYS.COLUMNNAME = \'DEGREELEVEL\' "
				+" LEFT JOIN COUNTRY C ON V.COUNTRYID = C.COUNTRYID "
				+" WHERE V.DEPARTMENTID IN(62,68,70,77,84,91,97,124,335,354,393) AND V.STAFFGROUP     = 1"
//				+" WHERE V.DEPARTMENTID IN(62) AND V.STAFFGROUP     = 1"
				+" ORDER BY SYS.BYTEDES ASC";
		System.out.println(sql);
		return getData(sql);
	}
	
	private ResultSet getVHrmStaffEduHighestDataDetail(String deptId){
		String sql =
				"SELECT V.TMALE, V.TFEMALE, V.APMALE,V.APFEMALE,V.SPMALE,V.SPFEMALE,V.PMALE,V.PFEMALE,V.NULLMALE,V.NULLFEMALE, V.DEPARTMENTID , "
				+" V.PREFIXNAME,V.STAFFNAME,V.STAFFSURNAME,V.ACADPOSITIONID,A.ACADPOSITIONNAME,V.DEGREELEVEL,SYS.BYTEDES,V.DEGREEID,D.DEGREENAME, "
				+" V.MAJORID,M.MAJORNAME,V.UNIVERSITYID,U.UNIVERSITYNAME,V.COUNTRYID,C.COUNTRYNAME "
				+" FROM V_HRM_STAFFEDU_HIGHEST V LEFT JOIN ACADPOSITION A ON V.ACADPOSITIONID = A.ACADPOSITIONID  "
				+" LEFT JOIN  MAJOR M ON V.MAJORID = M.MAJORID "
				+" LEFT JOIN STAFF S ON V.STAFFID = S.STAFFID "
				+" LEFT JOIN DEGREE D ON V.DEGREEID = D.DEGREEID "
				+" LEFT JOIN UNIVERSITY U ON V.UNIVERSITYID = U.UNIVERSITYID "
				+" LEFT JOIN SYSBYTEDES SYS ON V.DEGREELEVEL = SYS.BYTECODE AND SYS.TABLENAME = \'DEGREE\' AND SYS.COLUMNNAME = \'DEGREELEVEL\' "
				+" LEFT JOIN COUNTRY C ON V.COUNTRYID = C.COUNTRYID "
				+" WHERE V.DEPARTMENTID = "+deptId+" AND V.STAFFGROUP     = 1"
				+" ORDER BY SYS.BYTEDES ASC";
		return getData(sql);
	}
	
	private ResultSet getData(String sql){
		ResultSet result = null;
		try {
			/*ConnectionDB.getInstance();
			Connection con = ConnectionDB.getMisConnection();*/
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private double getPercenttage(int amountStaffbyType,int totalStaff){
		if(totalStaff>0){
			DecimalFormat dfm = new DecimalFormat("###.##");
			return Double.parseDouble(dfm.format((amountStaffbyType*100.00)/totalStaff));
		}else{
			return 0.0;
		}
	}
}
